package net.wickywicky.ps12;

import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class PS12DataHelper extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 2;
    private static final String DATABASE_NAME = "PS12";

    PS12DataHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS Bands");
		db.execSQL("DROP TABLE IF EXISTS Gigs");
        db.execSQL("CREATE TABLE " + "Bands" + " (" +
                "id" + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "name" + " TEXT," +
                "score" + " INTEGER);"
                );
        db.execSQL("CREATE TABLE Gigs (" +
                   "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   "scenario TEXT," +
                   "strt DATETIME," +
                   "band INTEGER);"
        		   );
    }

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub
		arg0.execSQL("DROP TABLE IF EXISTS Bands");
		arg0.execSQL("DROP TABLE IF EXISTS Gigs");
        onCreate(arg0);
	}
	
	public List<String> getBandList() {
		SQLiteDatabase db = getWritableDatabase();
		Cursor c;
		List<String> list = new ArrayList<String>();
		/*
      	c = db.query("Gigs", new String[] {"band, scenario, strftime('%d-%m-%Y',start)"}, 
  				 null, null, null, null, null); 
      			c.moveToFirst();
      			while (c.isAfterLast() == false) {
      				list.add(c.getString(0) + " " + c.getString(1) + " " + c.getString(2));
      				c.moveToNext();
      			}
      	c.close();
		*/
      	c = db.query("Bands", new String[] {"name"}, 
       				 null, null, null, null, null); 
      	c.moveToFirst();
        while (c.isAfterLast() == false) {
            list.add(c.getString(0));
            c.moveToNext();
        }
        c.close();
        
        
        
        return list;
	}

	public List<String> getBandGigs( String bandname ) {
		SQLiteDatabase db = getWritableDatabase();
		Cursor c;
		List<String> list = new ArrayList<String>();
		
		c = db.rawQuery("SELECT Gigs.scenario, strftime('%d-%m-%Y',Gigs.strt) FROM Gigs, Bands WHERE Gigs.band=Bands.id and Bands.name='" + bandname.replaceAll("'", "''")  + "'", null);
      	c.moveToFirst();
      	while (c.isAfterLast() == false) {
      		list.add(c.getString(0) + " " + c.getString(1));
     		c.moveToNext();
      	}
      	c.close();
                
        return list;
	}

	
	public void updateBands( List<String> bands ) {
		SQLiteDatabase db = getWritableDatabase();
		Iterator<String> iterator = bands.iterator();
        while(iterator.hasNext()){
        	String bandname = iterator.next().replace("'", "''");
        	Cursor c = db.query("Bands", new String[] {"id"}, 
        				 "name='" + bandname + "'", 
        				 null, null, null, null); 
        	if (c.getCount() == 0) {
        		db.execSQL("INSERT INTO Bands(name,score) VALUES ('" + bandname + "',0)");
        	}
        	c.close();
        }
        
	}
	
	public void addNewGig(String band, Date when, String scenario) {
		Format formatter = new SimpleDateFormat("yyyy-MM-dd");
		SQLiteDatabase db = getWritableDatabase();
		Cursor c;
		c = db.rawQuery("SELECT id FROM Bands WHERE name='" + band.replaceAll("'", "''") + "'", null);
		if (c.getCount() == 1) {
			c.moveToFirst();
			int bandid = c.getInt(0);
			//Log.v("hola","hola");
			Log.v("hola","SELECT * FROM Gigs WHERE band=" + bandid + " AND " +
								   		"strt = '" + formatter.format(when) + "' AND " +
								   		"scenario = '" + scenario.replaceAll("'", "''") + "'");
			c = db.rawQuery("SELECT * FROM Gigs WHERE band=" + bandid + " AND " +
								   		"strt = '" + formatter.format(when) + "' AND " +
								   		"scenario = '" + scenario.replaceAll("'", "''") + "'", null);
			//Log.v("hola","hola2");
	        if (c.getCount() == 0) {
	        	//Log.v("hola","hola3");
	        	db.execSQL("INSERT INTO Gigs (band,strt,scenario) " +
	        					"VALUES (" + bandid + ",'" + formatter.format(when) + "','" + scenario.replaceAll("'", "''") + "')");
	        	//Log.v("hola","adeu");

	        }
		}

        c.close();
	}
}
